﻿USE [iKBSystem]
GO

----- sp_GetLastStatus -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetLastStatus]'))
DROP PROCEDURE [dbo].[sp_GetLastStatus]
GO
CREATE PROCEDURE sp_GetLastStatus(
	@DomainScopeKey [uniqueidentifier],
	@DomainScopeUserKey [uniqueidentifier]
)
AS
BEGIN
	SELECT TOP 1 [Key],[Status],[StartStamp],[EndStamp]
	FROM dbo.[IMStatus]
	WHERE [DomainScopeKey] = @DomainScopeKey AND [DomainScopeUserKey] = @DomainScopeUserKey 
	ORDER BY [StartStamp] DESC;
END
GO

----- sp_GetDomainScopeUsersWithStatus -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetDomainScopeUsersWithStatus]'))
DROP PROCEDURE [dbo].[sp_GetDomainScopeUsersWithStatus]
GO
CREATE PROCEDURE sp_GetDomainScopeUsersWithStatus(
	@DomainScopeKey [uniqueidentifier]	--Optional
)
AS
DECLARE @SqlExp AS NVARCHAR(MAX);
DECLARE @WhereExp AS NVARCHAR(MAX);
BEGIN
	SET @SqlExp = '
	SELECT ufi.[DomainScopeUserKey]
      ,ufi.[DomainScopeKey]
	  ,ufi.[PersonKey]
	  ,ufi.[Description]
	  ,ufi.[ThirdPartyIdentity]
	  ,ufi.[MonitorStatus]
	  ,ufi.[CreatedStamp]
	  ,ufi.[LastUpdatedStamp]
	  ,ufi.[DomainScopeName]
      ,ufi.[Status]
      ,ufi.[StartStamp]
      ,ufi.[EndStamp] 
      FROM [dbo].[View_UserFullInfo] ufi';

	SET @WhereExp = '';
	IF(@DomainScopeKey IS NOT NULL)
		SET @WhereExp=@WhereExp+' ufi.[DomainScopeKey] = '''+CONVERT(NVARCHAR(130),@DomainScopeKey) + '''';
	IF(@WhereExp <> '')
		SET @SqlExp=@SqlExp+' WHERE '+ @WhereExp;
	SET @SqlExp=@SqlExp+' ORDER BY ufi.[DomainScopeKey];';

	EXEC(@SqlExp);
END
GO

----- sp_ReportStatus -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ReportStatus]'))
DROP PROCEDURE [dbo].[sp_ReportStatus]
GO
CREATE PROCEDURE sp_ReportStatus(
	@DomainScopeKey [uniqueidentifier],
	@DomainScopeUserKey [uniqueidentifier],
	@Status [int],
	@Stamp [datetime]
)
AS
DECLARE @ErrerMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

DECLARE @LastStatus AS [INT];
DECLARE @LastEndStamp AS [DATETIME];
DECLARE @LastRecordKey AS [uniqueidentifier];
BEGIN
	IF @DomainScopeKey IS NOT NULL AND @DomainScopeUserKey IS NOT NULL
		BEGIN TRY
  		BEGIN TRANSACTION

			SELECT TOP 1 @LastRecordKey=[Key], @LastStatus=[Status], @LastEndStamp=[EndStamp]
				FROM [dbo].[IMStatus]
				WHERE [DomainScopeKey]=@DomainScopeKey AND [DomainScopeUserKey]=@DomainScopeUserKey
				ORDER BY [StartStamp] DESC;
			
			IF(@LastRecordKey IS NULL OR @LastStatus <> @Status OR (DATEDIFF(s,@LastEndStamp,@Stamp) > 180))
				BEGIN
					--NEED INSERT NEW RECORD
					INSERT INTO [dbo].[IMStatus]([DomainScopeKey],[DomainScopeUserKey],[Status],[StartStamp],[EndStamp])
					VALUES(@DomainScopeKey, @DomainScopeUserKey, @Status, @Stamp, @Stamp);
				END
			ELSE
				BEGIN
					--JUST UPDATE
					UPDATE [dbo].[IMStatus]
					SET [EndStamp] = @Stamp
					WHERE [Key]= @LastRecordKey;
				END
			
			COMMIT TRANSACTION
		END TRY 
		BEGIN CATCH
			ROLLBACK TRANSACTION;
			SET @ErrerMessage = ERROR_MESSAGE();
			SET @ErrorSeverity = ERROR_SEVERITY();
			SET @ErrorState = ERROR_STATE();
			RAISERROR(@ErrerMessage,@ErrorSeverity,@ErrorState);
		END CATCH
END
GO

----- sp_QueryStatus -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_QueryStatus]'))
DROP PROCEDURE [dbo].[sp_QueryStatus]
GO
CREATE PROCEDURE sp_QueryStatus(
	@DomainScopeKey [uniqueidentifier],
	@DomainScopeUserKey [uniqueidentifier],
	@StartStamp [datetime],
	@EndStamp [datetime],
	@OrderDESC [bit]
)
AS
DECLARE @SqlExp AS NVARCHAR(MAX);
BEGIN
	SET @SqlExp='SELECT [Key],[DomainScopeKey],[DomainScopeUserKey],[Status],[StartStamp],[EndStamp]
	FROM dbo.[IMStatus] WHERE NOT(([StartStamp] < '''+CONVERT(NVARCHAR(100),@StartStamp)+''' AND [EndStamp] < '''+CONVERT(NVARCHAR(100),@StartStamp)+''')
		OR([StartStamp] > '''+CONVERT(NVARCHAR(100),@EndStamp)+''' AND [EndStamp] > '''+CONVERT(NVARCHAR(100),@EndStamp)+'''))';

	IF(@DomainScopeKey IS NOT NULL)
		SET @SqlExp=@SqlExp+' AND [DomainScopeKey] = '''+CONVERT(NVARCHAR(130),@DomainScopeKey) + '''';
	IF(@DomainScopeUserKey IS NOT NULL)
		SET @SqlExp=@SqlExp+' AND [DomainScopeUserKey] = '''+CONVERT(NVARCHAR(130),@DomainScopeUserKey) + '''';
	SET @SqlExp=@SqlExp+' ORDER BY [StartStamp]';

	IF(@OrderDESC = 1)
		SET @SqlExp=@SqlExp+' DESC';

	SET @SqlExp=@SqlExp+';';

	EXEC(@SqlExp);
END
GO

----- sp_QueryStatusEx -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_QueryStatusEx]'))
DROP PROCEDURE [dbo].[sp_QueryStatusEx]
GO
CREATE PROCEDURE [sp_QueryStatusEx](
	@DomainScopeCode [uniqueidentifier],
	@DomainScopeUserIdentity [uniqueidentifier],
	@StartStamp [datetime],
	@EndStamp [datetime],
	@OrderDESC [bit]
)
AS
BEGIN
	SET @SqlExp='SELECT ims2.[Key],ims2.[DomainScopeKey],ims2.[DomainScopeUserKey],ims2.[Status],ims2.[StartStamp],ims2.[EndStamp]
	FROM dbo.[IMStatus] ims2,(SELECT [DomainScopeUserKey] AS ''UserKey''
			,MAX([StartStamp]) AS ''LatestStamp''
		   FROM dbo.[IMStatus] AS ims
		GROUP BY ims.[DomainScopeUserKey]
	) AS R 
	WHERE R.UserKey= ims2.[DomainScopeUserKey] and R.[LatestStamp] = ims2.[StartStamp] AND NOT(([StartStamp] < '''+CONVERT(NVARCHAR(100),@StartStamp)+''' AND [EndStamp] < '''+CONVERT(NVARCHAR(100),@StartStamp)+''')
		OR([StartStamp] > '''+CONVERT(NVARCHAR(100),@EndStamp)+''' AND [EndStamp] > '''+CONVERT(NVARCHAR(100),@EndStamp)+'''))';

	IF(@DomainScopeKey IS NOT NULL)
		SET @SqlExp=@SqlExp+' AND [DomainScopeKey] = '''+CONVERT(NVARCHAR(130),@DomainScopeKey) + '''';
	IF(@DomainScopeUserKey IS NOT NULL)
		SET @SqlExp=@SqlExp+' AND [DomainScopeUserKey] = '''+CONVERT(NVARCHAR(130),@DomainScopeUserKey) + '''';
	SET @SqlExp=@SqlExp+' ORDER BY [StartStamp]';

	IF(@OrderDESC = 1)
		SET @SqlExp=@SqlExp+' DESC';

	SET @SqlExp=@SqlExp+';';	

	EXEC(@SqlExp);
END
GO

----- sp_MergeStatus -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_MergeStatus]'))
DROP PROCEDURE [dbo].[sp_MergeStatus]
GO
CREATE PROCEDURE sp_MergeStatus(
	@DomainScopeUserKey [uniqueidentifier],
	@StartGuid [uniqueidentifier],
	@EndGuid [uniqueidentifier],
	@Status [int]
)
AS
DECLARE @ErrerMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

DECLARE @StartStamp AS [DateTime];
DECLARE @EndStamp AS [DateTime];
BEGIN
	IF(@StartGuid IS NOT NULL AND @EndGuid IS NOT NULL)
		BEGIN TRY
  		BEGIN TRANSACTION
			SELECT TOP 1  @StartStamp = [StartStamp] FROM dbo.[IMStatus]
			WHERE [DomainScopeUserKey] = @DomainScopeUserKey AND [Key]=@StartGuid;

			SELECT TOP 1  @EndStamp = [EndStamp] FROM dbo.[IMStatus]
			WHERE [DomainScopeUserKey] = @DomainScopeUserKey AND [Key]=@EndGuid;

			IF(@StartStamp IS NOT NULL AND @EndStamp IS NOT NULL AND @StartStamp <= @EndStamp)
			BEGIN
				UPDATE dbo.[IMStatus]
				SET [EndStamp] = @EndStamp, [Status] = @Status
				WHERE [DomainScopeUserKey] = @DomainScopeUserKey AND [Key]=@StartGuid; 

				DELETE FROM dbo.[IMStatus]
				WHERE [DomainScopeUserKey] = @DomainScopeUserKey 
					AND [StartStamp] > @StartStamp AND [EndStamp] <= @EndStamp; 
			END
		COMMIT TRANSACTION
		END TRY 
		BEGIN CATCH
			ROLLBACK TRANSACTION;
			SET @ErrerMessage = ERROR_MESSAGE();
			SET @ErrorSeverity = ERROR_SEVERITY();
			SET @ErrorState = ERROR_STATE();
			RAISERROR(@ErrerMessage,@ErrorSeverity,@ErrorState);
		END CATCH
END
GO
